#region Copyright (c) 2006-2012 Widgetsphere LLC, All Rights Reserved
//--------------------------------------------------------------------- *
//                          Widgetsphere  LLC                           *
//             Copyright (c) 2006-2012 All Rights reserved              *
//                                                                      *
//                                                                      *
//This file and its contents are protected by United States and         *
//International copyright laws.  Unauthorized reproduction and/or       *
//distribution of all or any portion of the code contained herein       *
//is strictly prohibited and will result in severe civil and criminal   *
//penalties.  Any violations of this copyright will be prosecuted       *
//to the fullest extent possible under law.                             *
//                                                                      *
//THE SOURCE CODE CONTAINED HEREIN AND IN RELATED FILES IS PROVIDED     *
//TO THE REGISTERED DEVELOPER FOR THE PURPOSES OF EDUCATION AND         *
//TROUBLESHOOTING. UNDER NO CIRCUMSTANCES MAY ANY PORTION OF THE SOURCE *
//CODE BE DISTRIBUTED, DISCLOSED OR OTHERWISE MADE AVAILABLE TO ANY     *
//THIRD PARTY WITHOUT THE EXPRESS WRITTEN CONSENT OF WIDGETSPHERE LLC   *
//                                                                      *
//UNDER NO CIRCUMSTANCES MAY THE SOURCE CODE BE USED IN WHOLE OR IN     *
//PART, AS THE BASIS FOR CREATING A PRODUCT THAT PROVIDES THE SAME, OR  *
//SUBSTANTIALLY THE SAME, FUNCTIONALITY AS ANY WIDGETSPHERE PRODUCT.    *
//                                                                      *
//THE REGISTERED DEVELOPER ACKNOWLEDGES THAT THIS SOURCE CODE           *
//CONTAINS VALUABLE AND PROPRIETARY TRADE SECRETS OF WIDGETSPHERE,      *
//INC.  THE REGISTERED DEVELOPER AGREES TO EXPEND EVERY EFFORT TO       *
//INSURE ITS CONFIDENTIALITY.                                           *
//                                                                      *
//THE END USER LICENSE AGREEMENT (EULA) ACCOMPANYING THE PRODUCT        *
//PERMITS THE REGISTERED DEVELOPER TO REDISTRIBUTE THE PRODUCT IN       *
//EXECUTABLE FORM ONLY IN SUPPORT OF APPLICATIONS WRITTEN USING         *
//THE PRODUCT.  IT DOES NOT PROVIDE ANY RIGHTS REGARDING THE            *
//SOURCE CODE CONTAINED HEREIN.                                         *
//                                                                      *
//THIS COPYRIGHT NOTICE MAY NOT BE REMOVED FROM THIS FILE.              *
//--------------------------------------------------------------------- *
#endregion
using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using Widgetsphere.Generator.Models;
using Widgetsphere.Generator.ProjectItemGenerators;
using Widgetsphere.Generator.Common;

namespace Widgetsphere.Generator.Installer.ProjectItemGenerators.Functions
{
	class FunctionsTemplate : BaseDbScriptTemplate
	{
		private StringBuilder sb = new StringBuilder();

		#region Constructors
		public FunctionsTemplate(ModelRoot model)
		{
			_model = model;
		}
		#endregion

		#region BaseClassTemplate overrides
		public override string FileContent
		{
			get
			{
				this.GenerateContent();
				return sb.ToString();
			}
		}

		public override string FileName
		{
			get { return "Functions.sql"; }
		}
		#endregion

		#region GenerateContent
		private void GenerateContent()
		{
			if (_model.Database.AllowZeroTouch) return;
			try
			{
				this.AppendFullTemplate();
			}
			catch (Exception ex)
			{
				throw;
			}
		}

		#endregion

		#region string methods

		protected string BuildSelectList(Function function)
		{
			var output = new StringBuilder();
			int ii = 0;
			foreach (var column in function.GeneratedColumns)
			{
				ii++;
				output.Append(column.DatabaseName.ToLower());
				if (ii != function.GeneratedColumns.Count())
				{
					output.Append("," + Environment.NewLine + "\t");
				}
			}
			return output.ToString();
		}

		protected string BuildParameterList(Function function)
		{
			var output = new StringBuilder();
			var parameterList = function.GetParameters().Where(x => x.Generated && x.SortOrder > 0).OrderBy(x => x.SortOrder).ToList();
			parameterList.AddRange(function.GetParameters().Where(x => x.Generated && x.SortOrder == 0).OrderBy(x => x.Name).ToList());

			int ii = 0;
			foreach (var parameter in parameterList)
			{
				ii++;
				output.Append("\t@" + ValidationHelper.MakeDatabaseScriptIdentifier(parameter.DatabaseName) + " " +
					parameter.DatabaseType.ToLower() +
					(parameter.GetPredefinedSize() == -1 ? "(" + parameter.GetLengthString() + ") " : string.Empty) + (parameter.IsOutputParameter ? " out " : " = null"));

				if (ii != parameterList.Count)
					output.Append(",");
				output.AppendLine();
			}
			return output.ToString();
		}

		#endregion

		public void AppendFullTemplate()
		{
			try
			{
				sb.AppendLine("--DO NOT MODIFY THIS FILE. IT IS ALWAYS OVERWRITTEN ON GENERATION.");
				sb.AppendLine("--Model Version " + _model.Version);
				sb.AppendLine();
				sb.AppendLine("--This SQL is generated for functions");
				sb.AppendLine();
				Widgetsphere.Generator.GenerationHelper.AppendCopyrightInSQL(sb, _model);

				foreach (var function in _model.Database.Functions.Where(x => x.Generated).OrderBy(x => x.DatabaseName))
				{
					sb.AppendLine("if exists (select * from dbo.sysobjects where id = object_id(N'[" + function.GetSQLSchema() + "].[" + function.PascalName + "]') and [xtype] IN ('FN', 'IF', 'TF'))");
					sb.AppendLine("	drop function [" + function.GetSQLSchema() + "].[" + function.PascalName + "]");
					sb.AppendLine("GO");
					sb.AppendLine();
					sb.AppendLine("SET QUOTED_IDENTIFIER ON ");
					sb.AppendLine("GO");
					sb.AppendLine("SET ANSI_NULLS ON");
					sb.AppendLine("GO");
					sb.AppendLine();
					sb.AppendLine("CREATE FUNCTION [" + function.GetSQLSchema() + "].[" + function.PascalName + "]");

					sb.AppendLine("(");
					if (function.Parameters.Count > 0)
						sb.Append(this.BuildParameterList(function));
					sb.AppendLine(")");

					sb.Append("RETURNS ");

					if (function.IsTable && string.IsNullOrEmpty(function.ReturnVariable))
					{
						//There is NOT a returned table defined. This is a straight select
						sb.AppendLine("TABLE");
					}
					else if (function.IsTable && !string.IsNullOrEmpty(function.ReturnVariable))
					{
						//There is a returned table defined
						sb.Append("@" + function.ReturnVariable + " TABLE (");

						var columnList = function.GetColumns().Where(x => x.Generated).ToList();
						foreach (var column in columnList)
						{
							sb.Append(column.DatabaseName + " " + column.DatabaseType);
							if (columnList.IndexOf(column) < columnList.Count - 1) sb.Append(", ");
						}
						sb.AppendLine(")");
					}
					else
					{
						var column = function.Columns.First().Object as FunctionColumn;
						sb.AppendLine(column.DatabaseType.ToLower());
					}
					sb.AppendLine("AS");

					sb.AppendLine();
					sb.Append(function.SQL);
					sb.AppendLine();
					sb.AppendLine("GO");
					sb.AppendLine();
					if (!string.IsNullOrEmpty(_model.Database.GrantExecUser))
						if (!string.IsNullOrEmpty(_model.Database.GrantExecUser))
						{
							if (function.IsTable) sb.AppendFormat("GRANT SELECT ON [" + function.GetSQLSchema() + "].[{0}] TO [{1}]", function.PascalName, _model.Database.GrantExecUser).AppendLine();
							else sb.AppendFormat("GRANT EXECUTE ON [" + function.GetSQLSchema() + "].[{0}] TO [{1}]", function.PascalName, _model.Database.GrantExecUser).AppendLine();
							sb.AppendLine("GO");
							sb.AppendLine();
						}

				}
			}
			catch (Exception ex)
			{
				throw;
			}
		}

	}
}